Mysql有那些變數？如何設定？以及有那些值得認識的配置選項呢？

2021 iThome 鐵人賽

DAY 2

自我挑戰組

那些Mysql我不知道的事系列第 2 篇

13th鐵人賽

百里雪焱

2021-09-03 17:02:31

1718 瀏覽

分享至

前文提到mac電腦啟動mysql的方式為

brew services start mysql

其實我們也可以執行可執行檔(/usr/local/mysql/bin/mysqld)的方式來啟動
(補充：Windows系統的安裝目錄可能為：C:\Program Files\MySQL\MySQL Server8.0[這邊替換成你的版本號])

(base) ➜  ~ cd /usr/local/mysql/bin 
(base) ➜  bin mysqld
2021-09-03T03:08:18.913099Z 0 [System] [MY-010116] [Server] /usr/local/Cellar/mysql/8.0.26/bin/mysqld (mysqld 8.0.26) starting as process 40516
2021-09-03T03:08:18.999095Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2021-09-03T03:08:19.224466Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-09-03T03:08:19.648026Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-09-03T03:08:19.875857Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-09-03T03:08:19.889520Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-09-03T03:08:19.910900Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-09-03T03:08:19.911360Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-09-03T03:08:19.914875Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-09-03T03:08:19.915505Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-09-03T03:08:19.921172Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/usr/local/var/mysql' in the path is accessible to all OS users. Consider choosing a different directory.
2021-09-03T03:08:19.964443Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-09-03T03:08:19.964512Z 0 [System] [MY-010931] [Server] /usr/local/Cellar/mysql/8.0.26/bin/mysqld: ready for connections. Version: '8.0.26'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew.

這邊要注意的是如果要關掉服務，我試了ctrl+Z、C都無法關閉，因此我是直接kill -9 40516把程序砍掉的。我想這應該有更好的做法，如有大大們告訴我該怎麼做實在萬分感謝。

進入今天的正題，mysql有兩種變數

系統變數(我們可以設定調整)
狀態變數(我們不能調整，純粹查看伺服器狀態)

系統變數

有2種調整mysql系統變數的方式

第一種命令列下參數

(base) ➜  ~ mysqld --default-storage-engine=MyISAM

可以看到預設儲存引擎變更為MyISAM

mysql> show variables like 'default%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci    |
| default_password_lifetime     | 0                     |
| default_storage_engine        | MyISAM                |
| default_table_encryption      | OFF                   |
| default_tmp_storage_engine    | InnoDB                |
| default_week_format           | 0                     |
+-------------------------------+-----------------------+
7 rows in set (0.02 sec)

只在當次啟動生效唷!因次每次都要重新指定參數，很麻煩呀，因此常態性的設定我們可以考慮第二種方式-使用設定檔

第二種使用設定檔

設定檔需要自行建立，而系統吃設定檔也是有其固定目錄順序
這邊以mac電腦列出以下幾個為例(由上到下優先生效)
(1)/etc/my.cnf
(2)/etc/mysql/my.cnf
(3)defaults-extra-file[mysqld --default-extra-file=你的設定檔目錄]
(4)~/.my.cnf[特定於使用者的選項]

我這邊在~/.my.cnf建立設定檔(只對目前使用者生效)

(base) ➜  ~ vi ~/.my.cnf

//檔案內容為
[server]
default-storage-engine=MyISAM
max-connections=10

砍掉程序再重新啟動登入後可以看到設定值已調整

mysql> show variables like 'default%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci    |
| default_password_lifetime     | 0                     |
| default_storage_engine        | MyISAM                |
| default_table_encryption      | OFF                   |
| default_tmp_storage_engine    | InnoDB                |
| default_week_format           | 0                     |
+-------------------------------+-----------------------+
7 rows in set (0.01 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 10    |
+-----------------+-------+
1 row in set (0.00 sec)

主要就是這兩種方式
此外你可以針對不同的用戶端設定不同的參數(透過GLOBAL[全域] or SESSION[當前的連接]的方式)
像這樣子

mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.04 sec)

mysql> show global variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.02 sec)

mysql> set session default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

mysql> show global variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.01 sec)

只變更了當前連接(SESSION)的儲存引擎

而要改變global則是將session替換成global即可

mysql> set global default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> show global variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.03 sec)

要注意!並不是所有參數都有GLOBAL與SESSION的範圍
像max_connections只在GLOBAL而不在SESSION，而insert_id則只在SESSION不在GLOBAL唷。

狀態變數

用途是顯示系統狀態的，我們不能調整，跟系統變數一樣也有分GLOBAL與SESSION。
可以查看一些重要資訊:

mysql> show status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 2     |
| Threads_connected | 1     |
| Threads_created   | 3     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.04 sec)

Threads_cached 第一天有提到mysql斷開連接的時候不會馬上釋放掉執行緒，而是存在快取池內供下一個人使用。這參數就是快取池內可以給別人使用的空閒執行緒。
Threads_connected跟Threads_running這兩個可能會混淆一起說明。Threads_connected是連接的數量，而Threads_running是併發數量(也就是說只有正在執行CRUD動作時去查詢可能才看得出來)。
Threads_created 建立過的執行緒。這邊測試了一下執行語法新增資料庫或是表格也都會增加此數量。

mysql> show status like 'Innodb_rows_updated%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Innodb_rows_updated | 0     |
+---------------------+-------+
1 row in set (0.01 sec)

Innodb_rows_updated(更新了多少筆以Innodb為引擎表的資料)

由於變數相當的多，今天只是初略提到一些最基本的系統變數、狀態變數
會在未來實際上遇到有需要調整變數的情況再來更新此篇補充說明。

喔耶～可以去吃蒼蠅了